﻿/*----------------------------------------------
添加类型
----------------------------------------------*/
if exists(select 1 from T_Classification where ClassificationCode='System')
 begin
    print '【系统】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'System','系统')
 end

 if exists(select 1 from T_Classification where ClassificationCode='Frequency')
 begin
    print '【频段】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'Frequency','频段')
 end

 if exists(select 1 from T_Classification where ClassificationCode='NetWork')
 begin
    print '【网络】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'NetWork','网络')
 end

 if exists(select 1 from T_Classification where ClassificationCode='SimType')
 begin
    print '【卡槽】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'SimType','卡槽')
 end

 if exists(select 1 from T_Classification where ClassificationCode='ScreenPixel')
 begin
    print '【屏幕像素】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'ScreenPixel','屏幕像素')
 end

 if exists(select 1 from T_Classification where ClassificationCode='RAM')
 begin
    print '【内存RAM】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'RAM','内存RAM')
 end

 if exists(select 1 from T_Classification where ClassificationCode='ROM')
 begin
    print '【内存ROM】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'ROM','内存ROM')
 end

 if exists(select 1 from T_Classification where ClassificationCode='CameraFront')
 begin
    print '【摄像头前】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'CameraFront','摄像头前')
 end

if exists(select 1 from T_Classification where ClassificationCode='CameraBack')
 begin
    print '【摄像头后】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'CameraBack','摄像头后')
 end

if exists(select 1 from T_Classification where ClassificationCode='BatteryType')
 begin
    print '【电池】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'BatteryType','电池')
 end

if exists(select 1 from T_Classification where ClassificationCode='Earphone')
 begin
    print '【耳机】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'Earphone','耳机')
 end

if exists(select 1 from T_Classification where ClassificationCode='Charger')
 begin
    print '【充电器】已存在'
 end
else
 begin
	insert into T_Classification (ParentId,ClassificationCode,ClassificationName)
	values(0,'Charger','充电器')
 end


--添加菜单

declare @PModuleId varchar(40)
select @PModuleId=ModuleId from Sys_Module where ModuleCode='002'


if(exists(select 1 from Sys_Module where [ModuleCode]='002011'))
begin
  print '【系统分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('系统分类管理','002011',@PModuleId,1,'/Phone/Classification.aspx?Code=System',1,'11','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002012'))
begin
  print '【频段分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('频段分类管理','002012',@PModuleId,1,'/Phone/Classification.aspx?Code=Frequency',1,'12','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002013'))
begin
  print '【网络分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('网络分类管理','002013',@PModuleId,1,'/Phone/Classification.aspx?Code=NetWork',1,'13','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002014'))
begin
  print '【卡槽分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('卡槽分类管理','002014',@PModuleId,1,'/Phone/Classification.aspx?Code=SimType',1,'14','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002015'))
begin
  print '【屏幕像素分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('屏幕像素分类管理','002015',@PModuleId,1,'/Phone/Classification.aspx?Code=ScreenPixel',1,'15','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002016'))
begin
  print '【内存RAM分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('内存RAM分类管理','002016',@PModuleId,1,'/Phone/Classification.aspx?Code=RAM',1,'16','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002017'))
begin
  print '【内存ROM分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('内存ROM分类管理','002017',@PModuleId,1,'/Phone/Classification.aspx?Code=ROM',1,'17','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002018'))
begin
  print '【摄像头前分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('摄像头前分类管理','002018',@PModuleId,1,'/Phone/Classification.aspx?Code=CameraFront',1,'18','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002019'))
begin
  print '【摄像头后分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('摄像头后分类管理','002019',@PModuleId,1,'/Phone/Classification.aspx?Code=CameraBack',1,'19','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002020'))
begin
  print '【电池分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('电池分类管理','002020',@PModuleId,1,'/Phone/Classification.aspx?Code=BatteryType',1,'20','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002021'))
begin
  print '【耳机分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('耳机分类管理','002021',@PModuleId,1,'/Phone/Classification.aspx?Code=Earphone',1,'21','','001')
end

if(exists(select 1 from Sys_Module where [ModuleCode]='002022'))
begin
  print '【充电器分类管理】 已存在'
end
else
begin
	insert into Sys_Module([ModuleName],[ModuleCode],[ParentId],[Valid],[Href],[IsLeaf],[OrderNo],[TreeOrderNo],[SysNo])
	values('充电器分类管理','002022',@PModuleId,1,'/Phone/Classification.aspx?Code=Charger',1,'22','','001')
end



declare @tab_id  int
select @tab_id=object_id from sys.objects where name='T_Phone'

if(exists(select * from sys.all_columns where object_id=@tab_id and name='SystemId'))
begin
  print '表T_Phone里的字段已存在，不能再新增'
end
else
begin
	alter table T_Phone add SystemId int 
	alter table T_Phone add FrequencyId int
	alter table T_Phone add NetWorkId int
	alter table T_Phone add SimTypeId int
	alter table T_Phone add ScreenPixelId int
	alter table T_Phone add RAMId int
	alter table T_Phone add ROMId int
	alter table T_Phone add CameraFrontId int
	alter table T_Phone add CameraBackId int
	alter table T_Phone add BatteryTypeId int
	alter table T_Phone add EarphoneId int
	alter table T_Phone add ChargerId int

	alter table T_Phone add Color nvarchar(1000)
	alter table T_Phone add Accessories nvarchar(1000)
	alter table T_Phone add Size nvarchar(1000)


end



--更新视图
go

alter VIEW [dbo].[View_T_Phone]
AS
SELECT dbo.T_Phone.*,
	dbo.T_Supplier.SupplierName,
	dbo.T_Supplier.SupplierCode,
	AllPhoneTypeName=(select [dbo].[Fun_GetAllPhoneTypeName](dbo.T_Phone.PhoneId)),
	TopPictureSrc=(select top 1 Src from T_PhonePicture where dbo.T_PhonePicture.PhoneId=dbo.T_Phone.PhoneId order by IsTop desc),
	ScreenName=Screen.ClassificationName,
	ChipName=Chip.ClassificationName,
	SeriesName=Series.ClassificationName
FROM dbo.T_Phone
left outer join dbo.T_Supplier on dbo.T_Supplier.SupplierId=dbo.T_Phone.SupplierId
left outer join dbo.T_Classification as Screen 
	on Screen.ParentId in (select ClassificationId from dbo.T_Classification where ClassificationCode='Screen')
	and Screen.ClassificationId=dbo.T_Phone.ScreenId
left outer join dbo.T_Classification as Chip 
	on Chip.ParentId in (select ClassificationId from dbo.T_Classification where ClassificationCode='Chip')
	and Chip.ClassificationId=dbo.T_Phone.ChipId
left outer join dbo.T_Classification as Series 
	on Series.ParentId in (select ClassificationId from dbo.T_Classification where ClassificationCode='Series')
	and Series.ClassificationId=dbo.T_Phone.SeriesId

go